package com.database;

import java.sql.Connection;
import java.util.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import com.fileaction.FileBean;
import com.report.DayExchangeBean;
import com.report.ExchangeBean;
import com.userinfo.UserBean;


public class DataSearch {
	
	String sqlstr;
	String rsString;
	//String str2;
    Connection con=null;
	Statement stmt2=null;
	ResultSet rs=null;
	DataCon dt = new DataCon();
	
	public DataSearch(){}
	
	public List<FileBean> FileSearch() throws SQLException{
		con = dt.DbConn();
		stmt2 = con.createStatement();  
		sqlstr = "select * from filecontrol";
		System.out.println(sqlstr);
		rs = stmt2.executeQuery(sqlstr);
		List<FileBean> fileList=new ArrayList<FileBean>();
		while(rs.next()){
			//str2 = rs.getString("usrPass");
			FileBean fb=new FileBean();
			fb.setFileName(rs.getString("fileName"));
			fileList.add(fb);
		}
		//System.out.println(userList);
		return fileList;
	}
	
	public List<UserBean> DataSearch(String name) throws SQLException{
		con = dt.DbConn();
		stmt2 = con.createStatement();  
		System.out.println(name);
		sqlstr = "select * from userinfo where usrName = '"+ name +"'";
		System.out.println(sqlstr);
		rs = stmt2.executeQuery(sqlstr);
		List<UserBean> userList=new ArrayList<UserBean>();
		while(rs.next()){
			userList.add(new UserBean(rs.getString("usrName"),
                    rs.getString("usrPass"),rs.getDate("usrDate")));
			//str2 = rs.getString("usrPass");
		}
		//System.out.println(userList);
		
		return userList;
	}
	
	public List TDataSearch() throws Exception{
		con = dt.DbConn();
		stmt2 = con.createStatement();  
		sqlstr = "select * from businesssettlement";
		System.out.println(sqlstr);
		rs = stmt2.executeQuery(sqlstr);
		List reportList=new ArrayList();
		while(rs.next())
		{	
			Date date1=rs.getDate("balancestarttime");
			Date date2=rs.getDate("balanceendtime");
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String sDate1=sdf.format(date1);
			String sDate2=sdf.format(date2);
			System.out.println(rs.getDouble("othermanagementmoney"));
			reportList.add(new ExchangeBean(rs.getString("company"),rs.getString("remark"),sDate1,sDate2,
					rs.getDouble("totalincome"),rs.getDouble("Aticheng"),rs.getDouble("Apay"),rs.getDouble("electric"),rs.getDouble("water"),
					rs.getDouble("meiqi"),rs.getDouble("huanwei"),rs.getDouble("other"),rs.getDouble("fapiaomoney"),rs.getDouble("cashmoney"),
					rs.getDouble("creditmoney"),rs.getDouble("tuangoumoney"),rs.getDouble("tallymoney"),rs.getDouble("paymoney"),rs.getDouble("donemoney"),
					rs.getDouble("otherfoodmoney"),rs.getDouble("othermanagementmoney"),rs.getInt("fapiaobishu"),rs.getInt("cashbishu"),
					rs.getInt("creditbishu"),rs.getInt("tuangoubishu"),rs.getInt("tallybishu")));
			//Date date2 = rs.getDate("balancestarttime");
			//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			//String date5 = sdf.format(date2);
			//Date date3 = sdf.parse(date5.trim());
			//java.sql.Date sDate = new java.sql.Date(date3.getYear(),date3.getMonth(),date3.getDate());
			//eb.setCompany(rs.getString("company")
			//eb.setBalanceStartTime(rs.getDate("balancestarttime"));
			//System.out.println(rs.getDate("balancestarttime")+"----------"+rs.getDouble("fapiaomoney"));
		//	eb.setBalanceEndTime(rs.getDate("balanceendtime"));
		//	eb.setFapiaoMoney(rs.getDouble("fapiaomoney"));
		//	reportList.add(eb);
			//str2 = rs.getString("usrPass");
		}
		//System.out.println(userList);
		return reportList;
	}
	
	public List SDataSearch() throws SQLException{
		con = dt.DbConn();
		stmt2 = con.createStatement();  
		sqlstr = "select * from sheet1foodbuy";
		System.out.println(sqlstr);
		rs = stmt2.executeQuery(sqlstr);
		List reportList2=new ArrayList();
		while(rs.next())
		{
			//Date bstt = rs.getDate("buyDate");
			//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			//String date5 = sdf.format(bstt);
			//System.out.println(date5);
			DayExchangeBean deb=new DayExchangeBean();
			deb.setBuyDate(rs.getDate("buyDate"));
			deb.setBuyInfo(rs.getString("buyInfo"));
			deb.setBuyInfoWet(rs.getDouble("buyInfoWet"));
			reportList2.add(deb);
			//str2 = rs.getString("usrPass");
		}
		//System.out.println(userList);
		return reportList2;
	}

}
